﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace SchemaExplorer
{
    public class NamedObject
    {
        public readonly string Name;

        protected NamedObject(string name)
        {
            Name = name;
        }
    }


    public class TypedNamedObject : NamedObject
    {
        public readonly string Type;

        public Type GetMyType()
        {
            if (Type == null)
                return null;


            foreach (var typeString in new string[] { "int", "decimal", "money" })
                if (Type.Trim().ToLower().Contains(typeString))
                    return typeof(int);

            foreach (var typeString in new string[] { "char", "text" })
                if (Type.Trim().ToLower().Contains(typeString))
                    return typeof(string);


            foreach (var typeString in new string[] { "float", "real" })
                if (Type.Trim().ToLower().Contains(typeString))
                    return typeof(double);

            foreach (var typeString in new string[] { "time", "datetime" })
                if (Type.Trim().ToLower().Contains(typeString))
                    return typeof(DateTime);


            return null;

        }


        protected TypedNamedObject(string name, string type)
            : base(name)
        {
            Type = type;
        }

        public Type NetType
        {
            get { return GetMyType(); }
        }
    }


    public class Command : NamedObject
    {
        public readonly List<Parameter> Parameters;

        public Command(SqlConnection con, string dbName, string cmdName)
            : base(cmdName)
        {
            Parameters = new List<Parameter>();
            DataTable col = con.GetSchema("ProcedureParameters", new string[] { dbName, null, cmdName });
            foreach (DataRow row in col.Rows)
            {
                var parMode = row.Field<string>(col.Columns.IndexOf("PARAMETER_MODE"));
                string parType = row.Field<string>(col.Columns.IndexOf("DATA_TYPE"));
                string parName = row.Field<string>(col.Columns.IndexOf("SPECIFIC_NAME"));
                Parameters.Add(new Parameter(con, parName, parType, parMode));
            }
        }

        public override string ToString()
        {
            string retString = "Command: " + Name + "\n";
            retString += Parameters.Aggregate("Parameters:\n", (current, parameter) => current + "  " + parameter);
            return retString;
        }
    }

    public class Parameter : TypedNamedObject
    {
        public readonly string Mode;

        public Parameter(SqlConnection con, string prmName, string prmType, string prmMode)
            : base(prmName, prmType)
        {
            Mode = prmMode;
        }


        public override string ToString()
        {
            return "Parameter: " + Name + " of type: " + Type + " mode: " + Mode + " \n";
        }
    }


    public class View : NamedObject
    {
        public readonly List<Column> Columns;

        public View(SqlConnection con, string dbName, string vwName)
            : base(vwName)
        {
            Columns = new List<Column>();

            DataTable col = con.GetSchema("Columns", new string[] { dbName, null, vwName });
            foreach (DataRow row in col.Rows)
            {
                string colName = row.Field<string>(col.Columns.IndexOf("COLUMN_NAME"));
                string colType = row.Field<string>(col.Columns.IndexOf("DATA_TYPE"));
                int colPosition = row.Field<int>(col.Columns.IndexOf("ORDINAL_POSITION"));
                string colDefault = row.Field<string>(col.Columns.IndexOf("COLUMN_DEFAULT"));
                string colNullable = row.Field<string>(col.Columns.IndexOf("IS_NULLABLE"));

                Columns.Add(new Column(colName, colPosition, colDefault, colNullable, colType));
            }
        }

        public override string ToString()
        {
            string retString = "View: " + Name + "\n";
            if (Columns.Count > 0)
                retString += Columns.Aggregate("Columns:\n", (column, retStr) => retStr + "  " + column);
            return retString;
        }
    }

    public class ForeignKey : TypedNamedObject
    {
        public readonly List<Column> Columns;
        public readonly Table Parent;

        public ForeignKey(string conName, string conType)
            : base(conName, conType)
        {
        }

        public override string ToString()
        {
            return "ForeignKey " + Name + " of type: " + Type + "\n";
        }
    }


    public class Key : NamedObject
    {
        public readonly List<Column> Columns;
        public readonly Table Parent;

        public Key(string name, Table parent, List<string> columnNames)
            : base(name)
        {
            Columns = new List<Column>();
            Parent = parent;

            foreach (var column in Parent.Columns)
            {
                foreach (var columnName in columnNames)
                {
                    if (column.Name.ToLower().Trim().Equals(columnName.ToLower().Trim()))
                        Columns.Add(column);
                }
            }
        }

        public override string ToString()
        {

            string retString = "Key: " + Name + "\n";
            if (Columns.Count > 0)
                retString += Columns.Aggregate("KeyColumns:\n", (retStr, column) => retStr + "  " + column);
            return retString;

        }
    }

    public class Index : NamedObject
    {
        public Index(string name)
            : base(name)
        {
        }

        public override string ToString()
        {
            return "Index: " + Name + "\n";
        }
    }

    public class Column : TypedNamedObject
    {
        public readonly string Default;
        public readonly string Nullable;
        public readonly int Position;

        public Column(string colName, int colPos, string colDefault, string colNullable,
                      string colType)
            : base(colName, colType)
        {
            Position = colPos;
            Default = colDefault;
            Nullable = colNullable;
        }

        public override string ToString()
        {
            return "Column: " + Name + " of type: " + Type + " default: " + Default + " nullable: " + Nullable +
                   " Position in table: " + Position + "\n";
        }
    }


    public class Table : NamedObject
    {
        public readonly List<Column> Columns;
        public readonly List<ForeignKey> ForeignKeys;
        public readonly List<Index> Indexes;


        public Table(SqlConnection con, string dbName, string tblName)
            : base(tblName)
        {
            Columns = new List<Column>();
            Indexes = new List<Index>();
            ForeignKeys = new List<ForeignKey>();


            DataTable col = con.GetSchema("Columns", new string[] { dbName, null, tblName });
            foreach (DataRow row in col.Rows)
            {
                string colName = row.Field<string>(col.Columns.IndexOf("COLUMN_NAME"));
                string colType = row.Field<string>(col.Columns.IndexOf("DATA_TYPE"));
                int colPosition = row.Field<int>(col.Columns.IndexOf("ORDINAL_POSITION"));
                string colDefault = row.Field<string>(col.Columns.IndexOf("COLUMN_DEFAULT"));
                string colNullable = row.Field<string>(col.Columns.IndexOf("IS_NULLABLE"));

                Columns.Add(new Column(colName, colPosition, colDefault, colNullable, colType));
            }

            DataTable idx = con.GetSchema("Indexes", new string[] { dbName, null, null, tblName });

            foreach (DataRow row in idx.Rows)
            {
                string idxName = row.Field<string>(idx.Columns.IndexOf("constraint_name"));

                Indexes.Add(new Index(idxName));
            }

            DataTable fky = con.GetSchema("ForeignKeys", new string[] { dbName });
            foreach (DataRow row in fky.Rows)
            {
                string fkyName = row.Field<string>(fky.Columns.IndexOf("CONSTRAINT_NAME"));
                string fkyType = row.Field<string>(fky.Columns.IndexOf("CONSTRAINT_TYPE"));
                ForeignKeys.Add(new ForeignKey(fkyName, fkyType));
            }

            GetPrimaryKey(con);
        }

        public Key PrimaryKey { get; private set; }


        private void GetPrimaryKey(SqlConnection conn)
        {
            List<string> keyCols = new List<string>();
            string keyName = "";

            SqlCommand mSqlCommand = new SqlCommand("sp_pkeys", conn);
            mSqlCommand.CommandType = CommandType.StoredProcedure;
            mSqlCommand.Parameters.Add(
                "@table_name", SqlDbType.NVarChar).Value = Name;
            SqlDataReader mReader = mSqlCommand.ExecuteReader();
            while (mReader.Read())
            {
                keyCols.Add(mReader[3].ToString());
                keyName = mReader[5].ToString();
            }
            mReader.Close();

            PrimaryKey = new Key(keyName, this, keyCols);
        }




        public override string ToString()
        {
            string retString = "Table: " + Name + "\n";
            if (Columns.Count > 0)
                retString += Columns.Aggregate("Columns:\n", (retStr, column) => retStr + "  " + column);

            if (Indexes.Count > 0)
                retString += Indexes.Aggregate("Indexes:\n", (retStr, index) => retStr + "  " + index);

            if (PrimaryKey != null)
                retString += "  " + PrimaryKey;

            if (ForeignKeys.Count > 0)
                retString += ForeignKeys.Aggregate("Foregin Keys:\n", (retStr, key) => retStr + "  " + key); ;
            return retString;
        }
    }


    public class Database : NamedObject
    {
        public readonly List<Command> Commands;
        public readonly DateTime CreationDate;
        public readonly int Id;

        public readonly List<Table> Tables;
        public readonly List<View> Views;

        public Database(SqlConnection con, string dbName, int dbId, DateTime dbCreationDate)
            : base(dbName)
        {
            Tables = new List<Table>();
            Views = new List<View>();
            Commands = new List<Command>();


            CreationDate = dbCreationDate;
            Id = dbId;

            DataTable tbl = con.GetSchema("Tables", new[] { dbName });
            foreach (DataRow row in tbl.Rows)
            {
                string tblName = row.Field<string>(tbl.Columns.IndexOf("TABLE_NAME"));
                Tables.Add(new Table(con, dbName, tblName));
            }

            DataTable vws = con.GetSchema("Views", new[] { dbName });
            foreach (DataRow row in vws.Rows)
            {
                string viwName = row.Field<string>(vws.Columns.IndexOf("TABLE_NAME"));
                Views.Add(new View(con, dbName, viwName));
            }

            DataTable cmd = con.GetSchema("Procedures", new[] { dbName });
            foreach (DataRow row in cmd.Rows)
            {
                string cmdName = row.Field<string>(cmd.Columns.IndexOf("ROUTINE_NAME"));
                Commands.Add(new Command(con, dbName, cmdName));
            }
        }


        public override string ToString()
        {
            string retString = "Database: " + Name + "\n";
            if (Tables.Count > 0)
                retString += Tables.Aggregate("Tables:\n", (str, table) => str + "  " + table);
            if (Views.Count > 0)
                retString += Views.Aggregate("Views:\n", (str, view) => str + "  " + view);
            if (Commands.Count > 0)
                retString += Commands.Aggregate("Commands:\n", (str, command) => str + "  " + command);

            return retString;
        }
    }


    public sealed class SchemaExplorer
    {
        public readonly List<Database> Databases;

        public SchemaExplorer(SqlConnection con)
        {
            Databases = new List<Database>();


            if (con.State != ConnectionState.Open)
                return;

            DataTable dt = con.GetSchema("Databases");

            foreach (DataRow row in dt.Rows)
            {
                string dbName = row.Field<string>(dt.Columns.IndexOf("database_name"));
                int dbId = row.Field<Int16>(dt.Columns.IndexOf("dbid"));
                DateTime dbCreateDate = row.Field<DateTime>(dt.Columns.IndexOf("create_date"));
                Databases.Add(new Database(con, dbName, dbId, dbCreateDate));
            }

            Console.WriteLine(ToString());
        }


        public override string ToString()
        {
            string retString = "SchemaExplorer num databases:" + Databases.Count + "\n";
            if (Databases.Count > 0)
                retString += Databases.Aggregate(retString, (str, database) => str + database);
            return retString;
        }
    }
}